package com.wframe.framework.database;

import java.io.File;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

import javax.annotation.Resource;

import com.github.stuxuhai.jpinyin.PinyinException;
import com.github.stuxuhai.jpinyin.PinyinHelper;
import com.wframe.project.jnd.entity.Payment;
import com.wframe.project.jnd.entity.UiFloorItem;
import com.wframe.project.jnd.vo.PageContext;
import com.wframe.project.jnd.wx.constant.Constant;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.*;
import org.hibernate.transform.Transformers;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.frame.database.JdataBean;
import com.frame.database.PageResult;
import com.wframe.framework.spring.SpringContextHolder;


public class BaseDao {

    @Resource
    private SessionFactory sessionFactory;
    @Resource
    private JdbcTemplate jdbcTemplate;


    public List<JdataBean> querySql(String sql, Object[] parmobjs) {
        SQLQuery query = getSessionFactory().getCurrentSession().createSQLQuery(sql);
        if (parmobjs != null) {
            for (int i = 0; i < parmobjs.length; i++) query.setParameter(i, parmobjs[i]);
        }
        query.setResultTransformer(new AliasToBeanResultTransformer());
        return query.list();
    }

    public List querySql(String sql, Object[] parmobjs, Class entityType) {
        SQLQuery query = getSessionFactory().getCurrentSession().createSQLQuery(sql);
        if (parmobjs != null) {
            for (int i = 0; i < parmobjs.length; i++) query.setParameter(i, parmobjs[i]);
        }
        query.addEntity(entityType);
        return query.list();
    }

    public List<JdataBean> querySql2(String sql, List<Object> parmobjs) {
        SQLQuery query = getSessionFactory().getCurrentSession().createSQLQuery(sql);
        if (parmobjs != null) {
            for (int i = 0; i < parmobjs.size(); i++) query.setParameter(i, parmobjs.get(i));
        }
        query.setResultTransformer(new AliasToBeanResultTransformer());
        return query.list();
    }

    public List querySql2(String sql, List<Object> parmobjs, Class entityType) {
        SQLQuery query = getSessionFactory().getCurrentSession().createSQLQuery(sql);
        if (parmobjs != null) {
            for (int i = 0; i < parmobjs.size(); i++) query.setParameter(i, parmobjs.get(i));
        }
        query.addEntity(entityType);
        return query.list();
    }

    public List<Map> querySqlMap(String sql, Object[] parmobjs) {
        SQLQuery query = getSessionFactory().getCurrentSession().createSQLQuery(sql);
        if (parmobjs != null) {
            for (int i = 0; i < parmobjs.length; i++) {
                query.setParameter(i, parmobjs[i]);
            }
        }
        query.setResultTransformer(new AliasToMapResultTransformer());
        return query.list();
    }

    public List<Map> querySqlMap2(String sql, List<Object> parmobjs) {
        SQLQuery query = getSessionFactory().getCurrentSession().createSQLQuery(sql);
        if (parmobjs != null) {
            for (int i = 0; i < parmobjs.size(); i++) query.setParameter(i, parmobjs.get(i));
        }
        query.setResultTransformer(new AliasToMapResultTransformer());
        return query.list();
    }

    public PageResult querySql(String sql, Object[] parmobjs, int size, int pageno) {
        PageResult pr = new PageResult(size, pageno);
        Session session = getSessionFactory().getCurrentSession();
        SQLQuery query = session.createSQLQuery(sql);
        if (parmobjs != null) {
            for (int i = 0; i < parmobjs.length; i++) query.setParameter(i, parmobjs[i]);
        }
        query.setResultTransformer(new AliasToBeanResultTransformer());
        int start = size * (pageno - 1);
        query.setFirstResult(start);
        query.setMaxResults(size);
        int count = 0;
        SQLQuery countquery = session.createSQLQuery("select count(*) cc from (" + sql + ") t");
        if (parmobjs != null) {
            for (int i = 0; i < parmobjs.length; i++) countquery.setParameter(i, parmobjs[i]);
        }
        List clist = countquery.list();
        if (clist.size() > 0) count = Integer.parseInt(clist.get(0).toString());
        pr.setResultCount(count);
        pr.setList(query.list());
        return pr;
    }

    public PageResult querySql(String sql, Object[] parmobjs, int size, int pageno, Class entityType) {
        PageResult pr = new PageResult(size, pageno);
        Session session = getSessionFactory().getCurrentSession();
        SQLQuery query = session.createSQLQuery(sql);
        if (parmobjs != null) {
            for (int i = 0; i < parmobjs.length; i++) query.setParameter(i, parmobjs[i]);
        }
        query.addEntity(entityType);
        int start = size * (pageno - 1);
        query.setFirstResult(start);
        query.setMaxResults(size);
        int count = 0;
        SQLQuery countquery = session.createSQLQuery("select count(*) cc from (" + sql + ") t");
        if (parmobjs != null) {
            for (int i = 0; i < parmobjs.length; i++) countquery.setParameter(i, parmobjs[i]);
        }
        List clist = countquery.list();
        if (clist.size() > 0) count = Integer.parseInt(clist.get(0).toString());
        pr.setResultCount(count);
        pr.setList(query.list());
        return pr;
    }

    public PageResult querySql2(String sql, List<Object> parmobjs, int size, int pageno) {
        PageResult pr = new PageResult(size, pageno);
        Session session = getSessionFactory().getCurrentSession();
        SQLQuery query = session.createSQLQuery(sql);
        if (parmobjs != null) {
            for (int i = 0; i < parmobjs.size(); i++) {
                query.setParameter(i, parmobjs.get(i));
            }
        }
        query.setResultTransformer(new AliasToBeanResultTransformer());
        int start = size * (pageno - 1);
        query.setFirstResult(start);
        query.setMaxResults(size);
        int count = 0;
        SQLQuery countquery = session.createSQLQuery("select count(*) cc from (" + sql + ") t");
        if (parmobjs != null) {
            for (int i = 0; i < parmobjs.size(); i++) {
                countquery.setParameter(i, parmobjs.get(i));
            }
        }
        List clist = countquery.list();
        if (clist.size() > 0) {
            count = Integer.parseInt(clist.get(0).toString());
        }
        pr.setResultCount(count);
        pr.setList(query.list());
        return pr;
    }

    public PageResult querySql2(String sql, List<Object> parmobjs, int size, int pageno, Class entityType) {
        PageResult pr = new PageResult(size, pageno);
        Session session = getSessionFactory().getCurrentSession();
        SQLQuery query = session.createSQLQuery(sql);
        if (parmobjs != null) {
            for (int i = 0; i < parmobjs.size(); i++) query.setParameter(i, parmobjs.get(i));
        }
        query.addEntity(entityType);
        int start = size * (pageno - 1);
        query.setFirstResult(start);
        query.setMaxResults(size);
        int count = 0;
        SQLQuery countquery = session.createSQLQuery("select count(*) cc from (" + sql + ") t");
        if (parmobjs != null) {
            for (int i = 0; i < parmobjs.size(); i++) countquery.setParameter(i, parmobjs.get(i));
        }
        List clist = countquery.list();
        if (clist.size() > 0) count = Integer.parseInt(clist.get(0).toString());
        pr.setResultCount(count);
        pr.setList(query.list());
        return pr;
    }

    public PageResult querySqlMap(String sql, Object[] parmobjs, int size, int pageno) {
        PageResult pr = new PageResult(size, pageno);
        Session session = getSessionFactory().getCurrentSession();
        SQLQuery query = session.createSQLQuery(sql);
        if (parmobjs != null) {
            for (int i = 0; i < parmobjs.length; i++) query.setParameter(i, parmobjs[i]);
        }
        query.setResultTransformer(new AliasToMapResultTransformer());
        int start = size * (pageno - 1);
        query.setFirstResult(start);
        query.setMaxResults(size);
        int count = 0;
        SQLQuery countquery = session.createSQLQuery("select count(*) cc from (" + sql + ") t");
        if (parmobjs != null) {
            for (int i = 0; i < parmobjs.length; i++) countquery.setParameter(i, parmobjs[i]);
        }
        List clist = countquery.list();
        if (clist.size() > 0) count = Integer.parseInt(clist.get(0).toString());
        pr.setResultCount(count);
        pr.setList(query.list());
        return pr;
    }

    public PageResult querySqlMap2(String sql, List<Object> parmobjs, int size, int pageno) {
        PageResult pr = new PageResult(size, pageno);
        Session session = getSessionFactory().getCurrentSession();
        SQLQuery query = session.createSQLQuery(sql);
        if (parmobjs != null) {
            for (int i = 0; i < parmobjs.size(); i++) query.setParameter(i, parmobjs.get(i));
        }
        query.setResultTransformer(new AliasToMapResultTransformer());
        int start = size * (pageno - 1);
        query.setFirstResult(start);
        query.setMaxResults(size);
        int count = 0;
        SQLQuery countquery = session.createSQLQuery("select count(*) cc from (" + sql + ") t");
        if (parmobjs != null) {
            for (int i = 0; i < parmobjs.size(); i++) countquery.setParameter(i, parmobjs.get(i));
        }
        List clist = countquery.list();
        if (clist.size() > 0) count = Integer.parseInt(clist.get(0).toString());
        pr.setResultCount(count);
        pr.setList(query.list());
        return pr;
    }

    public JdataBean getObject(String sql, Object[] parmobjs) {
        List<JdataBean> list = querySql(sql, parmobjs);
        if (list.size() > 0) return list.get(0);
        else return null;
    }

    public JdataBean getObject2(String sql, List<Object> parmobjs) {
        List<JdataBean> list = querySql2(sql, parmobjs);
        if (list.size() > 0) return list.get(0);
        else return null;
    }

    public int save(String tableName, Map dataMap) throws Exception {
        String colList = "";//更新字段列表
        String valueList = "";//条件字段列表
        Object[] keys1 = null;
        int count1 = 0;
        if (dataMap == null || dataMap.size() == 0) {
            throw new Exception("没有要插入的字段和值！");
        }
        keys1 = dataMap.keySet().toArray();
        count1 = dataMap.size();
        for (int i = 0; i < count1; i++) {
            if (i != 0) {
                colList += ",";
                valueList += ",";
            }
            colList += keys1[i];
            valueList += "?";
        }
        String sql = "insert into " + tableName + "(" + colList + ") values (" + valueList + ")";
        SQLQuery query = getSessionFactory().getCurrentSession().createSQLQuery(sql);
        for (int i = 0; i < count1; i++) {
            Object val = dataMap.get(keys1[i]);
            if (val != null && "".equals(val.toString())) val = null;
            query.setParameter(i, val);
        }
        return query.executeUpdate();
    }

    public String saveReturnKey(String tableName, final Map dataMap) throws Exception {
        String colList = "";//更新字段列表
        String valueList = "";//条件字段列表
        Object[] keys1 = null;
        int count1 = 0;
        if (dataMap == null || dataMap.size() == 0) {
            throw new Exception("没有要插入的字段和值！");
        }
        keys1 = dataMap.keySet().toArray();
        count1 = dataMap.size();
        for (int i = 0; i < count1; i++) {
            if (i != 0) {
                colList += ",";
                valueList += ",";
            }
            colList += keys1[i];
            valueList += "?";
        }
        final String sql = "insert into " + tableName + "(" + colList + ") values (" + valueList + ")";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                Object[] keys1 = dataMap.keySet().toArray();
                for (int i = 0; i < keys1.length; i++) {
                    Object val = dataMap.get(keys1[i]);
                    if (val != null && "".equals(val.toString())) val = null;
                    ps.setObject(i + 1, val);
                }
                return ps;
            }
        }, keyHolder);
        return keyHolder.getKey().toString();
    }

    public void insertAcronymOfNameInProduct() throws Exception {
        String sql = "SELECT * FROM wb_products";
        Session currentSession = getSessionFactory().getCurrentSession();
        SQLQuery query = currentSession.createSQLQuery(sql);
        query.setResultTransformer(new AliasToBeanResultTransformer());
        batchUpdate("wb_products", query.list());
    }

    public int batchUpdate(String tableName, List<JdataBean> list) throws PinyinException {
        StringBuilder sql = new StringBuilder();
        StringBuilder setsbr = new StringBuilder();
        StringBuilder wheresbr = new StringBuilder();
        for (JdataBean bean : list) {
            setsbr.append(" WHEN '").append(bean.getInteger("id")).append("' THEN '").append(PinyinHelper.getShortPinyin(bean.getString("name"))).append("'");
            wheresbr.append(",'").append(bean.getInteger("id")).append("'");
        }
        sql.append("UPDATE ").append(tableName).append(" SET acronymname = CASE id ").append(setsbr).append(" END ").append(" WHERE id IN ").append(wheresbr.replace(0, 1, "(")).append(")");
        SQLQuery sqlQuery = getSessionFactory().getCurrentSession().createSQLQuery(sql.toString());
        return sqlQuery.executeUpdate();
    }

    public int update(String tableName, Map updateMap, Map keyMap) throws Exception {
        String setList = "";//更新字段列表
        String whereList = " and ";//条件字段列表
        Object[] keys1 = null;
        int count1 = 0;
        if (updateMap == null || updateMap.size() == 0) {
            throw new Exception("没有要更新的字段和值！");
        }
        keys1 = updateMap.keySet().toArray();
        count1 = updateMap.size();
        for (int i = 0; i < count1; i++) {
            if (i < count1 - 1) {
                setList += keys1[i] + "=?,";
            } else {
                setList += keys1[i] + "=?";
            }
        }
        Object[] keys2 = null;
        int count2 = 0;
        if (keyMap == null || keyMap.size() == 0) {
            whereList = "";
        } else {
            keys2 = keyMap.keySet().toArray();
            count2 = keyMap.size();
            for (int i = 0; i < count2; i++) {
                if (i < count2 - 1) {
                    whereList += keys2[i] + "=? and ";
                } else {
                    whereList += keys2[i] + "=?";
                }
            }
        }
        String sql = "update " + tableName + " set " + setList + " where 1=1"
                + whereList;
        SQLQuery query = getSessionFactory().getCurrentSession().createSQLQuery(sql);
        for (int i = 0; i < count1; i++) {
            Object obj = updateMap.get(keys1[i]);
            if (obj != null && "".equals(obj.toString())) obj = null;
            query.setParameter(i, obj);
        }
        if (!whereList.equals("")) {
            for (int i = 0; i < count2; i++) {
                Object obj = keyMap.get(keys2[i]);
                obj = obj == null ? "" : obj;
                query.setParameter(i + count1, obj);
            }
        }
        return query.executeUpdate();
    }

    public int execute(String sql, Object[] parmobjs) {
        SQLQuery query = getSessionFactory().getCurrentSession().createSQLQuery(sql);
        if (parmobjs != null) {
            for (int i = 0; i < parmobjs.length; i++) query.setParameter(i, parmobjs[i]);
        }
        return query.executeUpdate();
    }

    public int execute2(String sql, List<Object> parmobjs) {
        SQLQuery query = getSessionFactory().getCurrentSession().createSQLQuery(sql);
        if (parmobjs != null) {
            for (int i = 0; i < parmobjs.size(); i++) query.setParameter(i, parmobjs.get(i));
        }
        return query.executeUpdate();
    }

    public SessionFactory getSessionFactory() {
        return SpringContextHolder.getBean("sessionFactory");
//		return sessionFactory;
    }

    public void setSessionFactory(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
    }

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public Session getSession() {
        return sessionFactory.getCurrentSession();
    }
    /*
    public List<JdataBean> querySql(String sql,Object[] parmobjs){
		return (List<JdataBean>)jdbcTemplate.query(sql,  
				parmobjs, 
                new RowMapper() {  
                    @Override  
                    public Object mapRow(ResultSet rs, int rowNum) throws SQLException {  
                        JdataBean bean=new JdataBean();
                        ResultSetMetaData rsmd = rs.getMetaData();
                        for (int i=0;i<rsmd.getColumnCount();i++){
                        	String colname=rsmd.getColumnLabel(i+1);
                            Object obj=rs.getObject(colname);
                            bean.put(colname, obj);
                        }
                        return bean;  
                    }
                }  
        ); 
	}*/

    private SQLQuery createSQLQuery(String sql, Object obj) {
        SQLQuery query = getSessionFactory().getCurrentSession().createSQLQuery(sql);
        setProperties(query, obj);
        return query;
    }

    private SQLQuery createMapResultSQLQuery(String sql, Object obj) {
        SQLQuery query = createSQLQuery(sql, obj);
        query.setResultTransformer(new AliasToMapResultTransformer());
        return query;
    }

    private SQLQuery createBeanResultSQLQuery(String sql, Object obj) {
        SQLQuery query = createSQLQuery(sql, obj);
        query.setResultTransformer(new AliasToBeanResultTransformer());
        return query;
    }

    public SQLQuery createBeanResultSQLQuery(String sql, Object obj, Class<?> clazz) {
        SQLQuery query = createSQLQuery(sql, obj);
        query.setResultTransformer(Transformers.aliasToBean(clazz));
        return query;
    }

    private void setProperties(SQLQuery query, Object obj) {
        if (obj != null) {
            Class retType = obj.getClass();
            if (retType.isArray()) {
                setProperties(query, (Object[]) obj);
            } else if (Map.class.isAssignableFrom(retType)) {
                setProperties(query, (Map) obj);
            } else if (Collection.class.isAssignableFrom(retType)) {
                setProperties(query, (Collection) obj);
            } else {
                query.setParameter(0, obj);
            }
        }
    }

    private void setProperties(SQLQuery query, Collection collection) {
        if (collection != null) {
            setProperties(query, collection.toArray());
        }
    }

    private void setProperties(SQLQuery query, Map map) {
        String[] params = query.getNamedParameters();
        for (int i = 0; i < params.length; ++i) {
            String namedParam = params[i];
            Object object = map.get(namedParam);
            if (object != null) {
                Class retType = object.getClass();
                if (Collection.class.isAssignableFrom(retType)) {
                    query.setParameterList(namedParam, (Collection) object);
                } else if (retType.isArray()) {
                    query.setParameterList(namedParam, ((Object[]) object));
                } else {
                    query.setParameter(namedParam, object);
                }
            } else {
                query.setParameter(namedParam, object);
            }
        }
    }

    private void setProperties(SQLQuery query, Object[] arr) {
        if (arr != null) {
            for (int i = 0; i < arr.length; i++) {
                query.setParameter(i, arr[i]);
            }
        }
    }

    /**
     * 设置分页参数
     *
     * @param query
     * @param pageSize
     * @param pageNo
     */
    private void setPageProperty(Query query, int pageSize, int pageNo) {
        if (pageNo > 0 && pageSize > 0) {
            query.setFirstResult((pageNo - 1) * pageSize);
            query.setMaxResults(pageSize);
        }
    }

    /**
     * 获得总查询条数
     *
     * @return
     */
    public int getResultCount(String sql, Object obj) {
        Object count = uniqueColumnResult("select count(1) cc from (" + sql + ") t", obj);
        return Integer.parseInt(String.valueOf(count));
    }

    public PageResult paginationAndCountList(String sql, int pageSize, int pageNo, Object obj) {
        PageResult result = new PageResult(pageSize, pageNo);
        result.setList(paginationList(sql, pageSize, pageNo, obj));
        result.setResultCount(getResultCount(sql, obj));
        return result;
    }

    public List paginationList(String sql, int pageSize, int pageNo, Object obj) {
        SQLQuery query = createMapResultSQLQuery(sql, obj);
        setPageProperty(query, pageSize, pageNo);
        return getObject(query.list());
    }

    public <T> List<T> getObject(List<T> list) {
        if (list == null) {
            return Collections.EMPTY_LIST;
        }
        return list;
    }

    public List<Map> mapList(String sql, Object obj) {
        return paginationList(sql, 0, 0, obj);
    }

    public <T> List list(String sql, Object obj, Class<T> t) {
        SQLQuery query = createBeanResultSQLQuery(sql, obj, t);
        return getObject(query.list());
    }

    public List<JdataBean> list(String sql, Object obj) {
        SQLQuery query = createBeanResultSQLQuery(sql, obj);
        return getObject(query.list());
    }

    public List<Object> columnList(String sql, Object obj) {
        SQLQuery query = createSQLQuery(sql, obj);
        return getObject(query.list());
    }

    public Map uniqueMapResult(String sql, Object obj) {
        SQLQuery query = createMapResultSQLQuery(sql, obj);

        Object result = query.uniqueResult();
        if (result == null) {
            return Collections.EMPTY_MAP;
        }
        return (Map) result;
    }

    public Object uniqueColumnResult(String sql, Object obj) {
        SQLQuery query = createSQLQuery(sql, obj);
        return query.uniqueResult();
    }

    public JdataBean uniqueResult(String sql, Object obj) {
        SQLQuery query = createBeanResultSQLQuery(sql, obj);
        return (JdataBean) query.uniqueResult();
    }

    public <T> T uniqueResult(String sql, Object obj, Class<T> clazz) {
        SQLQuery query = createBeanResultSQLQuery(sql, obj, clazz);
        return (T) query.uniqueResult();
    }

    public Object findAttribute(String tableName, String columnName, String key, Object value) {
        String sql = new StringBuilder("SELECT ").append(columnName).append(" from ").append(tableName).append(" WHERE ").append(key).append("=?").toString();
        return uniqueColumnResult(sql, value);
    }

    public JdataBean findAttributes(String tableName, Map<String, Object> keyMap, String... columnName) {
        StringBuilder placeholder = new StringBuilder(" WHERE ");

        Set<Map.Entry<String, Object>> entries = keyMap.entrySet();
        Iterator<Map.Entry<String, Object>> iterator = entries.iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, Object> next = iterator.next();
            String key = next.getKey();
            Object value = next.getValue();
            Class retType = value.getClass();
            if (Collection.class.isAssignableFrom(retType)) {
                placeholder.append(key).append(" in(:").append(key).append(") and ");
            } else {
                placeholder.append(key).append(" =:").append(key).append(" and ");
            }
        }
        String sql = new StringBuilder("SELECT ").append(StringUtils.join(columnName, ",")).append(" from ").append(tableName).append(placeholder).append("1=1").toString();
        return uniqueResult(sql, keyMap);
    }

    public Object nextId(String tableName) {
        String sql = "SELECT IFNULL(MAX(id),0)+1 next FROM " + tableName;
        return uniqueColumnResult(sql, null);
    }

    public int delete(String tableName, Map<String, Object> keyMap) {
        StringBuilder placeholder = new StringBuilder("DELETE from ").append(tableName).append(" WHERE ");

        Set<Map.Entry<String, Object>> entries = keyMap.entrySet();
        Iterator<Map.Entry<String, Object>> iterator = entries.iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, Object> next = iterator.next();
            String key = next.getKey();
            Object value = next.getValue();
            Class retType = value.getClass();
            if (retType.isArray()) {
                placeholder.append(key).append(" in(:").append(key).append(") and ");
            } else if (Collection.class.isAssignableFrom(retType)) {
                placeholder.append(key).append(" in(:").append(key).append(") and ");
            } else {
                placeholder.append(key).append(" =:").append(key).append(" and ");
            }
        }
        String sql = placeholder.append("1=1").toString();
        return operate(sql, keyMap);
    }

    public int delete(String tableName, String key, Object val) {
        StringBuilder sql = new StringBuilder("delete from ").append(tableName).append(" WHERE ").append(key).append("=?");
        SQLQuery query = createSQLQuery(sql.toString(), val);
        return query.executeUpdate();
    }

    public int insert(String tableName, Map<String, Object> map) {
        StringBuilder columns = new StringBuilder();
        StringBuilder placeholder = new StringBuilder();
        List<Object> values = new ArrayList<>();

        Set<Map.Entry<String, Object>> entries = map.entrySet();
        Iterator<Map.Entry<String, Object>> iterator = entries.iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, Object> next = iterator.next();
            columns.append(",").append(next.getKey());
            placeholder.append(",?");
            values.add(next.getValue());
        }

        String sql = "INSERT INTO " + tableName + columns.replace(0, 1, "(").append(")").toString() + " VALUES " + placeholder.replace(0, 1, "(").append(")").toString();
        return operate(sql, values);
    }

    public int operate(String sql, Object obj) {
        SQLQuery query = createSQLQuery(sql, obj);
        return query.executeUpdate();
    }

    public int saveOrUpdate(String tableName, Object obj) throws IllegalAccessException {
        List<Object> values = null;
        String[] fragments = null;
        Class clazz = obj.getClass();
        if (Collection.class.isAssignableFrom(clazz)) {
            fragments = createSql(((Collection) obj).toArray()[0]);
            values = getValues((Collection) obj);
        } else {
            fragments = createSql(obj);
            values = getValues(obj);
        }
        String sql = "insert into " + tableName + fragments[0] + " VALUES " + fragments[1] + "on DUPLICATE key update " + fragments[2];
        return operate(sql, values);
    }

    private String[] createSql(Object obj) {
        StringBuilder columns = new StringBuilder();
        StringBuilder placeholder = new StringBuilder();
        StringBuilder targets = new StringBuilder();

        Class clazz = obj.getClass();
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            String name = field.getName();
            columns.append(",").append(name);
            placeholder.append(",?");
            targets.append(",").append(name).append("=VALUES(").append(name).append(")");
        }
        return new String[]{columns.replace(0, 1, "(").append(")").toString(),
                placeholder.replace(0, 1, "(").append(")").toString(),
                targets.replace(0, 1, "").toString()};
    }

    private List<Object> getValues(Object obj) throws IllegalAccessException {
        List<Object> values = new ArrayList<>();
        Class clazz = obj.getClass();
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            field.setAccessible(true);
            values.add(field.get(obj));
        }
        return values;
    }

    private List<Object> getValues(Collection obj) throws IllegalAccessException {
        List<Object> values = new ArrayList<>();
        Iterator iterator = obj.iterator();
        while (iterator.hasNext()) {
            Object next = iterator.next();
            Class clazz = next.getClass();
            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                values.add(field.get(obj));
            }
        }
        return values;
    }

}
